import pdfplumber
import pandas as pd
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook

global tables
import pyodbc
import re
import os


class PDFTableExtractor:
    def __init__(self, pdf_path):
        self.pdf_path = pdf_path
        self.report_name = os.path.basename(pdf_path).replace("：", ":")
        self.JZRQ = os.path.basename(pdf_path)
        self.JZRQ = re.findall(r'\d+', self.JZRQ)[0]
        if '半年度' in self.report_name:
            self.JZRQ = self.JZRQ + '06-30'

    # 识别表格(营收)
    def find_keyword_and_extract_table(self, keyword, IGSDM):
        self.IGSDM = IGSDM
        with pdfplumber.open(self.pdf_path) as pdf:
            for page_num in range(len(pdf.pages)):
                page = pdf.pages[page_num]
                text = page.extract_text()
                pattern = re.compile(r'单位：(.*)')

                DW_COUNT = []
                ##字段获取
                if page_num == 0:
                    lines = text.split('\n')
                    self.ZWMC = lines[2].strip()
                    print(self.report_name)  ##
                self.chixu = False
                if keyword in text:
                    matches = pattern.findall(text)
                    for match in matches:
                        DW_COUNT.append(match)
                        DW_COUNT = list(set(DW_COUNT))
                        if len(DW_COUNT) > 1:
                            print('该页有多个单位，请检查')
                        else:
                            self.DW = DW_COUNT[0].replace('单位', '') \
                                .replace(':', '') \
                                .replace('币种', '') \
                                .replace('：', '') \
                                .replace('人民币', '') \
                                .replace(' ', '')
                    self.chixu = False
                    print(f'在第 {page_num + 1} 页找到关键词 "{keyword}"。')
                    tables = self.extract_table_from_page(page)
                    excel_file = f'成本tables_page_{self.JZRQ}.xlsx'
                    self.save_tables_to_excel(tables, excel_file)
                    if self.chixu == True:
                        print('本页表格碰底')
                        page2 = pdf.pages[page_num + 1]
                        tables2 = self.concat_table2(page2)
                        if self.chixu2 == True:
                            a = pd.read_excel(f'成本tables_page_{self.JZRQ}.xlsx').shape[1]
                            tables = pd.read_excel(f'成本tables_page_{self.JZRQ}.xlsx')
                            b = pd.read_excel(f'tables2_page_{self.JZRQ}.xlsx').shape[1]
                            tables2 = pd.read_excel(f'tables2_page_{self.JZRQ}.xlsx')
                            tables.columns = range(len(tables.columns))
                            tables2.columns = range(len(tables2.columns))
                            print(f'上一页表格最大列数 {a}，下一页表格最大列数 {b}')
                            if a == b:
                                print('行数相等，拼表')
                                print(pd.isna(tables2[0][0]))
                                print(pd.isna(tables2[1][0]))
                                print(pd.isna(tables2[2][0]))
                                print(tables.iloc[:, 1].iloc[-1] == '营业收入')
                                print(pd.isna(tables2[0][0]) and pd.isna(tables2[1][0]) and pd.isna(tables2[2][0]) and
                                      tables.iloc[:, 1].iloc[-1] == '营业收入')
                                pattern = re.compile(r'^\d+(\.\d+)?$')
                                if not pd.isna(tables.iloc[:, 1].iloc[-1]):
                                    b = tables.iloc[:, 1].iloc[-1].replace(',', '')
                                    a = bool(pattern.match(b))
                                if (pd.isna(tables2[0][0]) and pd.isna(tables2[1][0]) and pd.isna(tables2[2][0]) and
                                        tables.iloc[:, 1].iloc[-1] == '营业收入'):
                                    print('极大可能指标！单元格跨页情况')
                                    tables2 = tables2.drop(0)
                                    tables.iloc[-1, 1:7] = ['营业收入', '营业成本', '毛利率（%）',
                                                            '营业收入比上年增减（%）',
                                                            '营业成本比上年增减（%）', '毛利率比上年增减（%）']
                                elif (pd.isna(tables2[0][0]) and pd.isna(tables2[1][0]) and pd.isna(
                                        tables2[2][0]) and pd.isna(tables2[3][0]) and pd.isna(
                                        tables2[4][0]) and pd.isna(tables2[5][0]) and not (pd.isna(tables2[6][0]))):
                                    print('极大可能百分点！单元格跨页情况')
                                    tables2 = tables2.drop(0)
                                    tables.iloc[:, 6].iloc[-1] = str(tables.iloc[:, 6].iloc[-1]) + str(
                                        tables2.iloc[:, 6].iloc[0])
                                tables.columns = range(len(tables.columns))
                                tables2.columns = range(len(tables2.columns))
                                result = pd.concat([tables, tables2], ignore_index=True)
                                self.save_tables_to_excel(result, excel_file)
                    return tables

    def extract_table_from_page(self, page):
        tables = page.extract_tables()
        if tables != None:  ##还没有变一维的表格判断
            print(f'长度{len(tables)}')
            if len(tables) >= 2 and len(tables) <= 3:
                for i, x in enumerate(tables):
                    if '本期金额' in x[1]:
                        tables = tables[i]
                        break
            elif len(tables) == 1:
                tables = tables[0]
            text = page.extract_text()
            lines = text.split('\n')
            last_line = lines[-2].strip()  ##本页倒数的文字
            last_table = tables[-1]  ##本表格倒数的文字
            last_table = ' '.join(str(item) for item in last_table if item is not None)
            Frist_line = lines[1].strip()  ##本页第一页文字
            Frist_table = tables[0][0]  ##本表格第一行文字
            Frist_table = ' '.join(str(item) for item in Frist_table if item is not None)
            self.chixu = last_line == last_table
            self.first = Frist_line == Frist_table
            for wenzi in last_line:
                if wenzi in last_table:
                    self.chixu = True
                    break
                else:
                    self.chixu = False
            tables = pd.DataFrame(tables)
        return tables

    # 拼接表格(营收)
    def concat_table2(self, page):
        tables2 = page.extract_tables()
        text = page.extract_text()
        lines = text.split('\n')
        Frist_line = lines[1].strip()
        Frist_table = tables2[0][0]
        Frist_table = ' '.join(str(item) for item in Frist_table if item is not None)
        self.chixu2 = Frist_line == Frist_table
        for wenzi in Frist_line:
            if wenzi in Frist_table:
                self.chixu2 = True
                print('下页表格触顶')
                break
            else:
                self.chixu2 = False
        print(f'self.chixu2:{self.chixu2}')
        excel_file = f'tables2_page_{self.JZRQ}.xlsx'
        for i, x in enumerate(tables2):
            if i == 0:
                a = x
                self.save_tables_to_excel(a, excel_file=excel_file)
                return a

    # 保存表格(营收)
    def save_tables_to_excel(self, tables, excel_file):
        with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
            sheet_name = '1'  # 初始 sheet_name 值
            df = pd.DataFrame(tables).replace('\n', '', regex=True)
            df.replace(' ', '')
            df = df.replace(' ', '')
            df.replace('', np.nan, inplace=True)
            # 获取每个字段的非空值数量
            non_null_count_per_column = df.count(axis=0)
            df = df.dropna(axis=1, how='all')
            for column in df.columns:
                # 判断当前列中是否包含搜索文本
                if df[column].astype(str).str.contains('主营业务分销售模式情况').any():
                    first_matching_row = df[df[column].astype(str).str.contains('主营业务分销售模式情况')].index[0]
                    print(f'在列 "{column}" 中找到匹配的文本，第一条数据在索引 {first_matching_row}。')
                    if column != 0:
                        df = df.drop(column, axis=1)
                        df[0][first_matching_row] = '主营业务分销售模式情况'
                        new_columns = ['0', '1', '2',
                                       '3', '4', '5', '6']
                        df = df.rename(columns=dict(zip(df.columns, new_columns)))
                    else:
                        new_columns = ['0', '1', '2',
                                       '3', '4', '5', '6']
                        df = df.rename(columns=dict(zip(df.columns, new_columns)))
                    break
            sheet_name = f'{1}'
            df.to_excel(writer, sheet_name=sheet_name, index=False)
            print(f'Table {sheet_name} saved to {excel_file} ')
            self.Table_num = 1
            print(f'All tables saved to {excel_file}')

    # 切割表格(营收)
    def sp(self, keywords):
        print('开始分割')
        CW = pd.read_excel(f'成本tables_page_{self.JZRQ}.xlsx').replace(' ', '')
        null_positions = CW[CW[0].isnull()].index
        print("空值的位置：", null_positions)
        CW[0].fillna(method='ffill', inplace=True)

        if any(CW.isin(['分行业情况', '分产品情况'])):
            new_df = CW
            new_df.columns = range(len(new_df.columns))
            n = 0
            BASCI_split_indices = [0]
            split_indices = new_df[
                new_df[0].apply(lambda x: any(keyword in x for keyword in keywords))].index.tolist()
            split_indices = [int(str(index).replace('[', '').replace(']', '')) for index in split_indices]
            split_indices = BASCI_split_indices + split_indices
            if split_indices:
                split_indices.append(len(new_df))
                with pd.ExcelWriter('成本分割表格.xlsx', engine='xlsxwriter') as writer:
                    for i in range(0, len(split_indices)):
                        if split_indices[i] > 0:
                            dfs = new_df.iloc[split_indices[i - 1] + 1:split_indices[i]]
                            sheet_name = dfs[0].iloc[0]
                            dfs.to_excel(writer, sheet_name=sheet_name, index=False)
            else:
                print("No split indices found.")

    # 获取代码类字段(营收)
    def get_code(self):
        db_name = r'C:\Users\yuankf47185\PycharmProjects\客户端尝试\mydatabase.db'
        con = connect(db_name)
        Product = "SELECT * FROM product_code"
        self.PRODUCT_CODE = pd.read_sql(Product, con)
        ZB = 'SELECT * FROM ZB_CODE'
        self.ZB_CODE = pd.read_sql(ZB, con)
        DQ = 'SELECT * FROM city_code'
        self.DQ_CODE = pd.read_sql(DQ, con)
        BT = 'SELECT * FROM GG  '
        self.BT = pd.read_sql(BT, con)
        con.close()

    # 格式化为企业经营的底层表(营收)
    def xggs(self):
        excel_file_path = '成本分割表格.xlsx'
        new_excel_file_path = '单年成本企业经营.xlsx'
        workbook = openpyxl.load_workbook(excel_file_path)
        sheet_names = workbook.sheetnames
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
            for i, sheet_name in enumerate(sheet_names, start=1):
                print(f'Processing sheet {i}')
                yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ', '', regex=True)
                if '分产品' in sheet_names and '分行业' in sheet_names and sheet_name == '分行业':
                    continue
                new_columns = yysr.iloc[0].replace('分产品', 'SJLMYMC').replace('分行业', 'SJLMYMC').replace(
                    '成本构成项目', 'SJLMSMC')
                id_vars = ['SJLMYMC', 'SJLMSMC']
                yysr = yysr.rename(columns=new_columns)
                yysr = yysr.drop(0)
                yysr = yysr.drop('情况说明', axis=1).drop('本期占总成本比例(%)', axis=1).drop('上年同期金额', axis=1) \
                    .drop('上年同期占总成本比例(%)', axis=1)
                yysr = pd.melt(yysr,
                               id_vars=['SJLMYMC', 'SJLMSMC'],
                               value_vars=list(yysr.columns)[1:],
                               var_name='ZBMC',
                               value_name='ZBSJ')

                yysr['JZRQ'] = None
                yysr['ZTYSMC'] = None
                yysr['SJLMY'] = None
                yysr['EPBH'] = None
                yysr['ZBDW'] = None
                yysr['SJLME'] = None
                yysr['SJLMS'] = None
                yysr['TJKJ'] = None
                yysr['SFYX'] = None
                yysr['SFYX'].iloc[:len(yysr)] = 'FCC000000006'
                conditions = [
                    (yysr['ZBMC'] == '本期金额'),
                    (yysr['ZBMC'] == ('本期金额较上年同期变动比例(%)'))
                ]
                values = ['当期值', '当期同比增长率']
                yysr['TJKJ'] = np.select(conditions, values, default='当期值')

                DW_VALUES = [self.DW, '%']
                yysr['ZBDW'] = np.select(conditions, DW_VALUES)
                yysr['TJQJ'] = '年'
                yysr['JYYWLXDM'] = None
                yysr['XXLY'] = None
                yysr = yysr.replace('（%）', '', regex=True).replace('比上年增减', '', regex=True).replace('营业收入',
                                                                                                         '销售收入',
                                                                                                         regex=True).astype(
                    str)
                yysr['ZBSJ'] = yysr['ZBSJ'].str.replace('减少', '-', regex=True).replace('增加', '', regex=True) \
                    .replace('个百分点', '', regex=True).replace('不适用', '', regex=True).replace('/', '', regex=True)
                yysr['SJLMYDM'] = yysr.merge(self.PRODUCT_CODE, on='SJLMYMC', how='left')['标准代码']
                yysr['SJLMY'].iloc[:len(yysr)] = '产品'
                yysr['JYYWLXDM'].iloc[:len(yysr)] = '成本构成'
                yysr['SJLMYMC'] = yysr['SJLMYMC'].replace('合计', '产品合计', regex=True).replace('减:', '', regex=True)
                yysr['SJLMSMC'] = yysr['SJLMSMC'].replace('合计', '').replace('减:', '', regex=True)
                empty_strings = yysr['SJLMSMC'].eq('')

                yysr.loc[yysr['SJLMSMC'].notna() & (yysr['SJLMSMC'] != ''), 'SJLMS'] = '其他'
                yysr['ZBMC'] = yysr['ZBMC'].replace('本期金额', '成本').replace('本期金额较上年同期变动比例(%)', '成本')
                yysr['ZBDM'] = yysr.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
                yysr['EPBH'].iloc[:len(yysr)] = str(self.EPBH)
                yysr['JZRQ'].iloc[:len(yysr)] = self.JZRQ + '-12-31'
                yysr['XXLY'].iloc[:len(yysr)] = str(self.report_name).replace('.pdf', '')
                yysr['XXFBRQ'] = yysr.merge(self.BT, on='XXLY', how='left')['XXFBRQ']

                yysr = yysr.reindex(
                    columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
                             'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
                             'ZBDW', 'TJKJ', 'TJQJ'])
                yysr = yysr.replace(' ', '')

                yysr.to_excel(writer, index=False, sheet_name=sheet_name)

    # 五年合成一张表(营收)
    def concat_table(self):
        excel_data = pd.read_excel('单年成本企业经营.xlsx', sheet_name=None)
        merged_data = pd.DataFrame()
        for sheet_name, sheet_data in excel_data.items():
            merged_data = pd.concat([merged_data, sheet_data], ignore_index=True)
        output_excel_path = f'result_merged.xlsx'
        merged_data.to_excel(output_excel_path, index=False, sheet_name='拼接')

    # 获取EPBH(营收)
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                SELECT DISTINCT EPBH
                FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                WHERE QYBH=? 
                    """
        IGSDM = self.IGSDM
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'
        return self.EPBH


# 打包-连FLASK
def run_all(input_folder, IGSDM):
    # Update this with your actual folder path

    all_merged_data = pd.DataFrame()

    # Iterate over all PDF files in the specified folder
    for pdf_file in os.listdir(input_folder):
        if pdf_file.endswith('.pdf'):
            pdf_path = os.path.join(input_folder, pdf_file)
            # Process each PDF file
            pdf_table_extractor = PDFTableExtractor(pdf_path)
            pdf_table_extractor.get_code()
            keyword_to_find = '成本分析表'
            tables = pdf_table_extractor.find_keyword_and_extract_table(keyword_to_find, IGSDM)
            keywords = ['分行业情况', '分产品情况']
            if isinstance(tables, pd.core.frame.DataFrame):
                # some code here
                new_df = pdf_table_extractor.save_tables_to_excel(tables, 'output_file.xlsx')
                pdf_table_extractor.get_EPBH()
                processed_data = pdf_table_extractor.sp(keywords)
                pdf_table_extractor.xggs()
                pdf_table_extractor.concat_table()
                # Read the merged data from the current file and append it to the overall merged data
                current_merged_data = pd.read_excel('result_merged.xlsx', sheet_name='拼接')
                all_merged_data = pd.concat([all_merged_data, current_merged_data], ignore_index=True)
                EPBH = pdf_table_extractor.get_EPBH()
                output_excel_path_all = f'成本EPBH_{EPBH}.xlsx'
                print(f'{pdf_file}年报,拼接完成')
                all_merged_data.to_excel(output_excel_path_all, index=False, sheet_name='拼接_all')
                print(f'所有年报拼接完成，结果保存在 {output_excel_path_all}')
            else:
                print(f'{pdf_file}年报,未找到关键字')


if __name__ == '__main__':
    input_folder = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\files\735243242145.PDF'
    IGSDM = '232012'
    run_all(input_folder, IGSDM)

